package oop.db.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import oop.data.Examination;
import oop.db.Database;

public class ExaminationDAO {

	public static Examination fetchById(long id) throws SQLException {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String sql = "SELECT exam_id, exam_teacher, exam_test, exam_date, "
					+ "exam_name, exam_class " 
					+ "FROM tblExam "
					+ "WHERE exam_id = ?";

			conn = Database.get().getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setLong(1, id);
			rs = stmt.executeQuery();
	
			return fromResultSet(rs).get(0);
		} finally {
			if (rs != null) rs.close();
			if (stmt != null) stmt.close();
			if (conn != null) conn.close();
		}
	}

	public static List<Examination> fetchByTeacher(long teacherId, long start, int length) throws SQLException {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String sql = "SELECT exam_id, exam_teacher, exam_test, exam_date, "
					+ "exam_name, exam_class " 
					+ "FROM tblExam "
					+ "WHERE exam_teacher = ? " 
					+ "ORDER BY exam_date DESC "
					+ "LIMIT ?, ?";

			conn = Database.get().getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setLong(1, teacherId);
			stmt.setLong(2, start);
			stmt.setInt(3, length);
			rs = stmt.executeQuery();
	
			return fromResultSet(rs);
		} finally {
			if (rs != null) rs.close();
			if (stmt != null) stmt.close();
			if (conn != null) conn.close();
		}
	}

	public static long countByTeacher(long teacherId) throws SQLException {
		return Database.get().count("SELECT COUNT(*) " +
				"FROM tblExam " +
				"WHERE exam_teacher=" + teacherId);
	}

	public static List<Examination> fromResultSet(ResultSet rs)
			throws SQLException {
		List<Examination> examinations = new ArrayList<Examination>();
		while (rs.next()) {
			examinations.add(new Examination(rs.getLong(1), rs.getLong(2), rs
					.getLong(3), rs.getDate(4), rs.getString(5), rs
					.getString(6)));
		}
		return examinations;
	}
	
	public static void persist(Examination exam) throws SQLException {
		Connection conn = null;
		PreparedStatement stmt = null;
		try {
				String sql = "UPDATE tblExam SET " +
						"exam_test=?, exam_date=?, exam_name=?, exam_class=? " +
						"WHERE exam_id = ?";

				conn = Database.get().getConnection();
				stmt = conn.prepareStatement(sql);
				stmt.setLong(1, exam.getTest().getId());
				stmt.setDate(2, new java.sql.Date(exam.getDate().getTime()));
				stmt.setString(3, exam.getName());
				stmt.setString(4, exam.getClazz());
				stmt.setLong(5, exam.getId());
				stmt.executeUpdate();
		} finally {
			if (stmt != null) stmt.close();
			if (conn != null) conn.close();
		}
	}

	public static Examination create(long teacherId, long testId, Date date,
			String name, String clazz) throws SQLException {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String sql = "INSERT INTO tblExam (" +
					"exam_test, exam_date, exam_name, exam_class, exam_teacher) " +
					"VALUES (?, ?, ?, ?, ?)";

			conn = Database.get().getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setLong(1, testId);
			stmt.setDate(2, new java.sql.Date(date.getTime()));
			stmt.setString(3, name);
			stmt.setString(4, clazz);
			stmt.setLong(5, teacherId);
			stmt.executeUpdate();

			rs = stmt.getGeneratedKeys();
			rs.next();
			long id = rs.getLong(1);

			return new Examination(id, teacherId, testId, date, name, clazz);
		} finally {
			if (rs != null) rs.close();
			if (stmt != null) stmt.close();
			if (conn != null) conn.close();
		}
	}

	public static int drop(long examId) throws SQLException {
		Connection conn = null;
		PreparedStatement stmt = null;
		try {
			String sql = "DELETE FROM tblExam " +
					"WHERE exam_id = ?";

			conn = Database.get().getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setLong(1, examId);
			return stmt.executeUpdate();
		} finally {
			if (stmt != null) stmt.close();
			if (conn != null) conn.close();
		}		
	}

}
